package zyy.control;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import zyy.model.BeanMerchant;
import zyy.model.BeanProduct;
import zyy.model.BeanProduct_Dir;
import zyy.util.BusinessException;
import zyy.util.DBUtil;
import zyy.util.DbException;

public class ProductManager {
  public List<BeanProduct> loadByProduct_DirID(int productDirID) throws DbException {
    List<BeanProduct> result = new ArrayList<BeanProduct>();
    Connection conn = null;
    try {
      conn = DBUtil.getConnection();
      String sql = "SELECT ProductID,pName,pValue,pRealValue,MID,mName,store FROM Product WHERE productDirID=?";
      java.sql.PreparedStatement pst = conn.prepareStatement(sql);
      pst.setInt(1, productDirID);
      java.sql.ResultSet rs = pst.executeQuery();
      while (rs.next()) {
        BeanProduct p = new BeanProduct();
        p.setProductID(rs.getInt(1));
        p.setpName(rs.getString(2));
        p.setpValue(rs.getDouble(3));
        p.setpRealValue(rs.getDouble(4));
        p.setProductDirID(productDirID);
        p.setMID(rs.getInt(5));
        p.setmName(rs.getString(6));
        p.setStore(rs.getDouble(7));
        result.add(p);
      }
      rs.close();
      pst.close();
    } catch (SQLException e) {
      e.printStackTrace();
      throw new DbException(e);
    } finally {
      if (conn != null)
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
    }
    return result;
  }

  public void add(BeanMerchant m, BeanProduct_Dir pd, String pName, double pValue, double pRealValue, double store)
      throws BusinessException {
    Connection conn = null;
    try {
      conn = DBUtil.getConnection();
      conn.setAutoCommit(false);
      String sql = "INSERT INTO Product( MID, productDirID, mName, pName, pValue, pRealValue, store ) values(?, ?, ?, ?, ?, ?, ?)";
      java.sql.PreparedStatement pst = conn.prepareStatement(sql);
      pst.setInt(1, m.getMID());
      pst.setInt(2, pd.getProductDirID());
      pst.setString(3, m.getmName());
      pst.setString(4, pName);
      pst.setDouble(5, pValue);
      pst.setDouble(6, pRealValue);
      pst.setDouble(7, store);
      pst.executeUpdate();
      sql = "UPDATE Product_Dir SET pdCnt = pdCnt+1 WHERE productDirID=?";
      pst = conn.prepareStatement(sql);
      pst.setInt(1, pd.getProductDirID());
      pst.executeUpdate();
      pst.close();
      conn.commit();
    } catch (SQLException e) {
      System.out.println("出现SQL异常，开始回滚");
      try {
        conn.rollback();
        System.out.println("回滚成功");
      } catch (SQLException e1) {
        System.out.println("回滚失败！！！");
        throw new BusinessException(e1.getMessage());
      }
      e.printStackTrace();
      throw new BusinessException(e.getMessage());
    } finally {
      if (conn != null)
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
    }
  }

  public void unadd(BeanProduct p) throws BusinessException {
    // 先查，如果没有 OrderProject 没有对应 ProductID，再删除ProductID
    Connection conn = null;
    try {
      conn = DBUtil.getConnection();
      conn.setAutoCommit(false);
      String sql = "SELECT * FROM OrderProjects WHERE ProductID = ? AND OrderID NOT IN ( SELECT OrderID FROM Orders o WHERE o.oStatus IN ('已确认收货') ) LIMIT 0, 1";
      java.sql.PreparedStatement pst = conn.prepareStatement(sql);
      pst.setInt(1, p.getProductID());
      java.sql.ResultSet rs = pst.executeQuery();
      if (rs.next()) {
        throw new BusinessException("该商品尚有未完成的订单，不可删除");
      } else {
        System.out.println("删除商品实体");
        sql = "DELETE FROM Product WHERE ProductID = ?";
        pst = conn.prepareStatement(sql);
        pst.setInt(1, p.getProductID());
        pst.executeUpdate();
        sql = "UPDATE Product_Dir SET pdCnt=pdCnt-1 WHERE productDirID=?";
        pst = conn.prepareStatement(sql);
        pst.setInt(1, p.getProductDirID());
        pst.execute();
      }
      rs.close();
      pst.close();
      conn.commit();
    } catch (SQLException e) {
      System.out.println("出现SQL异常，开始回滚");
      try {
        conn.rollback();
        System.out.println("回滚成功");
      } catch (SQLException e1) {
        System.out.println("回滚失败！！！");
        throw new BusinessException(e1.getMessage());
      }
      e.printStackTrace();
      throw new BusinessException(e.getMessage());
    } finally {
      if (conn != null)
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
    }
  }
}